﻿Imports System.Windows.Forms
Imports System.ComponentModel
Imports System.Collections.Specialized

Public Class Balance1

    Private Sub btn查询_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn查询.Click

        If CheckCondition() = False Then
            Exit Sub
        End If

        Dim dtNew As DataTable = GetData()
        If dtNew.Rows.Count = 0 Then
            Exit Sub
        End If

        LoadDataA
    End Sub

    Private Sub LoadDataA()

        Dim dtNew As DataTable = GetData()
        If dtNew.Rows.Count = 0 Then
            Exit Sub
        End If
        Dim dt1 As New DataTable
        For jdex As Int32 = 0 To dtNew.Columns.Count - 1
            dt1.Columns.Add(dtNew.Columns(jdex).ColumnName)
        Next

        For index As Integer = 0 To dtNew.Rows.Count - 1
            Dim dr As DataRow = dt1.NewRow
            dt1.Rows.Add(dr)
            For jdex As Integer = 0 To dtNew.Columns.Count - 1
                dt1.Rows(index)(jdex) = dtNew.Rows(index)(jdex).ToString
            Next
        Next

        For index As Integer = 0 To dt1.Rows.Count - 1
            dt1.Rows(index)("日期") = CDate(dt1.Rows(index)("日期")).ToString("yyyy-MM-dd")
        Next
        Me.OutlookGrid1.AutoGenerateColumns = False
        Me.OutlookGrid1.BindData(dt1, "收入")
        'Me.OutlookGrid1.Columns(0).Visible = False

        Dim al As New List(Of DataGridViewColumn)
        Dim al1 As New List(Of ListSortDirection)

        If al.Count = 0 Then
            al.Add(Me.OutlookGrid1.Columns("UserName"))
            al1.Add(ListSortDirection.Ascending)
        End If

        OutlookGrid1.GroupTemplate.Column = al.ToArray
        OutlookGrid1.Sort(al.ToArray)

        RadioButton1_CheckedChanged(Nothing, Nothing)

    End Sub
    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        '年
        dtData.Rows.Add(New String() {"开始时间", "", dt开始时间.Value.ToString("yyyy-MM-dd")})
        dtData.Rows.Add(New String() {"结束时间", "", dt结束时间.Value.ToString("yyyy-MM-dd")})

        If Me.rad全部展开.Checked Then
            dtData.Rows.Add(New String() {"全部展开", "", "是"})
        Else
            dtData.Rows.Add(New String() {"全部展开", "", "否"})
        End If

        MyBase.保存窗体数据到数据库(dtData)

    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

        If dtDbData.Rows.Count > 0 Then

            '年
            dt开始时间.Value = 查找数据("开始时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            dt结束时间.Value = 查找数据("结束时间", "", Now.ToString("yyyy-MM-dd"), dtDbData)
            rad全部展开.Checked = IIf(查找数据("全部展开", "", "是", dtDbData) = "是", True, False)
            rad全部折叠.Checked = IIf(查找数据("全部展开", "", "否", dtDbData) = "否", True, False)
        End If

        MyBase.存取数据库数据到窗体(dtDbData)
    End Sub

    Private Function CheckCondition() As Boolean

        Dim 开始时间 As Integer = CInt(dt开始时间.Value.ToString("yyyyMMdd"))
        Dim 结束时间 As Integer = CInt(dt结束时间.Value.ToString("yyyyMMdd"))
        If 开始时间 > 结束时间 Then
            Return False
        End If

        Return True

    End Function
    Private Function GetData() As DataTable

        Dim strBegin As String = Me.dt开始时间.Value.Year.ToString("0000") + "-" + Me.dt开始时间.Value.Month.ToString("00") + "-" + Me.dt开始时间.Value.Day.ToString("00")
        Dim strEnd As String = Me.dt结束时间.Value.Year.ToString("0000") + "-" + Me.dt结束时间.Value.Month.ToString("00") + "-" + Me.dt结束时间.Value.Day.ToString("00")

        Dim sb As New StringBuilder
        sb.AppendLine("SELECT Account_Detail.*,WysInputUser.UserName")
        sb.AppendLine("from Account_Detail")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("ON Account_Detail.用户帐户ID=WysInputUser.ID")
        sb.AppendLine("WHERE Account_Detail.日期>='" + strBegin + "' AND Account_Detail.日期<='" + strEnd + "'")
        sb.AppendLine("ORDER BY WysInputUser.UserIndex,Account_Detail.日期")

        Return ct.FillTable(sb.ToString())

    End Function

    Private Sub OutlookGrid1_SGT(ByRef content As System.String, ByVal olg As Wys.Control.CSharp.OutlookGridRow, ByVal RowIndex As System.Collections.Generic.List(Of Integer)) Handles OutlookGrid1.SGT
        If Not olg.IsLastRow = True Then

            Dim 支出金额 As Decimal = 0
            Dim 收入金额 As Decimal = 0
            Dim 借出金额 As Decimal = 0
            Dim 还入金额 As Decimal = 0

            Dim 借入金额 As Decimal = 0
            Dim 还出金额 As Decimal = 0
            Dim 转入金额 As Decimal = 0
            Dim 转出金额 As Decimal = 0

            Dim 冲帐金额 As Decimal = 0

            Dim username As String = ""
            For Each ri As Integer In RowIndex
                支出金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("支出").Value)
                收入金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("收入").Value)
                借出金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("借出").Value)
                还入金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("还入").Value)

                借入金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("借入").Value)
                还出金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("还出").Value)
                转入金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("转入").Value)
                转出金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("转出").Value)

                冲帐金额 += CDec(Me.OutlookGrid1.Rows(ri).Cells("冲账").Value)

                username = CStr(Me.OutlookGrid1.Rows(ri).Cells("username").Value)
            Next
            content = FillString(username, 8) & "  " & _
                            FillString("支出:" & 支出金额.ToString, 14) & _
                            FillString("收入:" & 收入金额.ToString, 14) & _
                            FillString("借出:" & 借出金额.ToString, 14) & _
                            FillString("还入:" & 还入金额.ToString, 14) & _
                            FillString("借入:" & 借入金额.ToString, 14) & _
                            FillString("还出:" & 还出金额.ToString, 14) & _
                            FillString("转入:" & 转入金额.ToString, 14) & _
                            FillString("转出:" & 转出金额.ToString, 14) & _
                            FillString("冲帐:" & 冲帐金额.ToString, 14)
        Else
            Dim 支出金额 As Decimal = 0
            Dim 收入金额 As Decimal = 0
            Dim 借出金额 As Decimal = 0
            Dim 还入金额 As Decimal = 0

            Dim 借入金额 As Decimal = 0
            Dim 还出金额 As Decimal = 0
            Dim 转入金额 As Decimal = 0
            Dim 转出金额 As Decimal = 0

            Dim 冲帐金额 As Decimal = 0
            For Each dr As DataGridViewRow In Me.OutlookGrid1.Rows
                支出金额 += CDec(dr.Cells("支出").Value)
                收入金额 += CDec(dr.Cells("收入").Value)
                借出金额 += CDec(dr.Cells("借出").Value)
                还入金额 += CDec(dr.Cells("还入").Value)

                借入金额 += CDec(dr.Cells("借入").Value)
                还出金额 += CDec(dr.Cells("还出").Value)
                转入金额 += CDec(dr.Cells("转入").Value)
                转出金额 += CDec(dr.Cells("转出").Value)

                冲帐金额 += CDec(dr.Cells("冲账").Value)

            Next
            content = FillString("总合计", 8) & "  " & _
                            FillString("支出:" & 支出金额.ToString, 14) & _
                            FillString("收入:" & 收入金额.ToString, 14) & _
                            FillString("借出:" & 借出金额.ToString, 14) & _
                            FillString("还入:" & 还入金额.ToString, 14) & _
                            FillString("借入:" & 借入金额.ToString, 14) & _
                            FillString("还出:" & 还出金额.ToString, 14) & _
                            FillString("转入:" & 转入金额.ToString, 14) & _
                            FillString("转出:" & 转出金额.ToString, 14) & _
                            FillString("冲帐:" & 冲帐金额.ToString, 14)
        End If
    End Sub

    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad全部折叠.Click, rad全部展开.Click

        If Me.rad全部折叠.Checked Then
            Me.OutlookGrid1.CollapseAll()
        Else
            Me.OutlookGrid1.ExpandAll()
        End If

    End Sub

    Private Sub Balance1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        LoadDataA()

    End Sub

End Class
